"""
Case Type   : SQL
Case Name   : 创建聚合函数时使用模式修饰
Create At   : 2023/5/23
Owner       : haomeng802
Description :
    1:创建模式
    2:创建聚合函数时指定模式名
    3:查看函数是否在指定的模式下
    4:创建聚合函数时不指定模式名
    5:查看函数是否在当前的模式下
    6:清理环境
    drop aggregate sch_aggregate_case0001.avg_case0001(float8) cascade;
    drop schema sch_aggregate_case0001 cascade;
    drop aggregate avg_case0001(float8) cascade;
Expect      :
    1:成功
    2:成功
    3:函数在指定的模式下
    4:成功函数在当前的模式下
    6:成功
History     : 
"""
import os
import unittest
from yat.test import macro
from yat.test import Node
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Common import Common


class Sql_Operation(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.commonsh = CommonSH('dbuser')
        self.constant = Constant()
        self.sh_name = f'sch_aggregate_case0001'
        self.agg_name = f'avg_case0001'

    def test_explain(self):
        text = '-----step1:创建模式;expect:成功-----'
        self.log.info(text)
        sql_cmd = self.commonsh.execut_db_sql(
            f'drop schema if exists {self.sh_name};'
            f'create schema {self.sh_name};')
        self.log.info(sql_cmd)
        self.assertIn(self.constant.CREATE_SCHEMA_SUCCESS_MSG, sql_cmd,
                      '执行失败' + text)

        text = '-----step2:创建聚合函数时指定模式名;expect:成功-----'
        self.log.info(text)
        sql_cmd = self.commonsh.execut_db_sql(
            f"drop aggregate if exists {self.sh_name}."
            f"{self.agg_name}(float8) cascade;"
            f"create aggregate {self.sh_name}.{self.agg_name}(float8)"
            f"(sfunc = float8_accum,"
            f" stype = float8[],"
            f" finalfunc = float8_avg,"
            f" initcond = '{0, 0, 0}' );")
        self.log.info(sql_cmd)
        self.assertIn(f'CREATE AGGREGATE', sql_cmd, '执行失败' + text)

        text = '-----step3:查看函数是否在指定的模式下;expect:函数在指定的模式下-----'
        self.log.info(text)
        sql_cmd = self.commonsh.execut_db_sql(
            f'\da {self.sh_name}.{self.agg_name}')
        self.log.info(sql_cmd)
        self.assertIn(f'{self.sh_name}', sql_cmd, '执行失败' + text)
        self.assertIn(f'{self.agg_name}', sql_cmd, '执行失败' + text)

        text = '-----step4:创建聚合函数时不指定模式名;expect:成功-----'
        self.log.info(text)
        sql_cmd = self.commonsh.execut_db_sql(
            f"drop aggregate if exists {self.agg_name}(float8) cascade; "
            f"create aggregate {self.agg_name}(float8)"
            f"(sfunc = float8_accum,"
            f" stype = float8[],"
            f" finalfunc = float8_avg,"
            f" initcond = '{0, 0, 0}' );")
        self.log.info(sql_cmd)
        self.assertIn(f'CREATE AGGREGATE', sql_cmd, '执行失败' + text)

        text = '-----step5:查看函数是否在当前的模式下;expect:函数在当前的模式下-----'
        self.log.info(text)
        sql_cmd = self.commonsh.execut_db_sql(
            f'select current_schema;\da {self.agg_name};')
        self.log.info(sql_cmd)
        self.assertIn('public', sql_cmd, '执行失败' + text)
        self.assertIn(f'{self.agg_name}', sql_cmd, '执行失败' + text)

    def tearDown(self):
        text = '-----step6:清理环境;expect:成功-----'
        self.log.info(text)
        sql_cmd = self.commonsh.execut_db_sql(
            f'drop aggregate {self.sh_name}.{self.agg_name}(float8) cascade;'
            f'drop schema {self.sh_name} cascade;'
            f'drop aggregate {self.agg_name}(float8) cascade;')
        self.log.info(sql_cmd)
        self.assertIn(self.constant.DROP_SCHEMA_SUCCESS_MSG,
                      sql_cmd, '执行失败' + text)
        self.assertIn(f'DROP AGGREGATE', sql_cmd, '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
